package org.csu.mypetstore.persistence;

//this is completed
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.csu.mypetstore.domain.Account;
import org.csu.mypetstore.domain.Wish;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface AccountMapper extends BaseMapper {
    @Select("  select\n" +
            "                      SIGNON.USERNAME,\n" +
            "                      SIGNON.PASSWORD,\n" +
            "                      ACCOUNT.EMAIL,\n" +
            "                      ACCOUNT.FIRSTNAME,\n" +
            "                      ACCOUNT.LASTNAME,\n" +
            "                      ACCOUNT.STATUS,\n" +
            "                      ACCOUNT.ADDR1 AS address1,\n" +
            "                      ACCOUNT.ADDR2 AS address2,\n" +
            "                       ACCOUNT.CITY,\n" +
            "                       ACCOUNT.STATE,\n" +
            "                      ACCOUNT.ZIP,\n" +
            "                      ACCOUNT.COUNTRY,\n" +
            "                      ACCOUNT.PHONE,\n" +
            "                      PROFILE.LANGPREF AS languagePreference,\n" +
            "                      PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "                      PROFILE.MYLISTOPT AS listOption,\n" +
            "                      PROFILE.BANNEROPT AS bannerOption,\n" +
            "                      BANNERDATA.BANNERNAME\n" +
            "                      FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
            "                      WHERE ACCOUNT.USERID = #{username}\n" +
            "                      AND SIGNON.USERNAME = ACCOUNT.USERID\n" +
            "                      AND PROFILE.USERID = ACCOUNT.USERID\n" +
            "                      AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY;")
    Account getAccountByUsername(String username);
    @Select("SELECT\n" +
            "      SIGNON.USERNAME,\n" +
            "      ACCOUNT.EMAIL,\n" +
            "      ACCOUNT.FIRSTNAME,\n" +
            "      ACCOUNT.LASTNAME,\n" +
            "      ACCOUNT.STATUS,\n" +
            "      ACCOUNT.ADDR1 AS address1,\n" +
            "      ACCOUNT.ADDR2 AS address2,\n" +
            "      ACCOUNT.CITY,\n" +
            "      ACCOUNT.STATE,\n" +
            "      ACCOUNT.ZIP,\n" +
            "      ACCOUNT.COUNTRY,\n" +
            "      ACCOUNT.PHONE,\n" +
            "      PROFILE.LANGPREF AS languagePreference,\n" +
            "      PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "      PROFILE.MYLISTOPT AS listOption,\n" +
            "      PROFILE.BANNEROPT AS bannerOption,\n" +
            "      BANNERDATA.BANNERNAME\n" +
            "    FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA\n" +
            "    WHERE ACCOUNT.USERID = #{username}\n" +
            "      AND SIGNON.PASSWORD = #{password}\n" +
            "      AND SIGNON.USERNAME = ACCOUNT.USERID\n" +
            "      AND PROFILE.USERID = ACCOUNT.USERID\n" +
            "      AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY")
    Account getAccountByUsernameAndPassword(Account account);
    @Insert("INSERT  INTO account  VALUES(#{username},#{email},#{firstName},#{lastName},#{status},#{address1},#{address2},#{city},#{state},#{zip},#{country},#{phone});")
    void insertAccount(Account account);
    @Insert("INSERT  INTO profile  VALUES(#{username},#{languagePreference},#{favouriteCategoryId},#{listOption},#{bannerOption});")
    void insertProfile(Account account);
    @Insert("INSERT  INTO  signon  VALUES(#{username},#{password});")
    void insertSignon(Account account);
    @Update(" UPDATE account SET userid=#{username} ,email = #{email} , firstname = #{firstName} , lastname = #{lastName},status=#{status},addr1=#{address1},addr2=#{address2},city=#{city},state=#{state},zip=#{zip},country=#{country},phone=#{phone} WHERE userid = #{username};")
    void updateAccount(Account account);
    @Update("UPDATE profile SET userid=#{username}, langpref = #{languagePreference} , favcategory = #{favouriteCategoryId} , mylistopt = #{listOption},banneropt=#{bannerOption} WHERE userid = #{username};")
    void updateProfile(Account account);
    @Update("UPDATE signon SET username=#{username}, password=#{password} WHERE username=#{username};")
    void updateSignon(Account account);
    @Select("select\n" +
            "                      ACCOUNT.USERID AS username,\n" +
            "                      SIGNON.PASSWORD AS password,\n" +
            "                      ACCOUNT.EMAIL,\n" +
            "                      ACCOUNT.FIRSTNAME,\n" +
            "                      ACCOUNT.LASTNAME,\n" +
            "                      ACCOUNT.STATUS,\n" +
            "                      ACCOUNT.ADDR1 AS address1,\n" +
            "                      ACCOUNT.ADDR2 AS address2,\n" +
            "                      ACCOUNT.CITY,\n" +
            "                      ACCOUNT.STATE,\n" +
            "                      ACCOUNT.ZIP,\n" +
            "                      ACCOUNT.COUNTRY,\n" +
            "                      ACCOUNT.PHONE FROM account,signon WHERE  ACCOUNT.USERID=SIGNON.USERNAME;")
    List<Account> getallAccount();
    @Select("SELECT\n" +
            "                    PROFILE.USERID AS  username,\n" +
            "                    PROFILE.LANGPREF AS languagePreference,\n" +
            "                    PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "                    PROFILE.MYLISTOPT AS listOption,\n" +
            "                    PROFILE.BANNEROPT AS bannerOption\n" +
            "                    FROM  PROFILE;")
    List<Account> getallAccountprofile();
    @Select("SELECT\n" +
            "                              ACCOUNT.USERID AS username,\n" +
            "                              ACCOUNT.EMAIL,\n" +
            "                              ACCOUNT.FIRSTNAME,\n" +
            "                              ACCOUNT.LASTNAME,\n" +
            "                              ACCOUNT.STATUS,\n" +
            "                              ACCOUNT.ADDR1 AS address1,\n" +
            "                              ACCOUNT.ADDR2 AS address2,\n" +
            "                              ACCOUNT.CITY,\n" +
            "                              ACCOUNT.STATE,\n" +
            "                              ACCOUNT.ZIP,\n" +
            "                              ACCOUNT.COUNTRY,\n" +
            "                              ACCOUNT.PHONE\n" +
            "                              FROM  account WHERE ACCOUNT.USERID LIKE #{keyword};")
    List<Account> searchAccount(String keyword);
    @Select("SELECT\n" +
            "                            PROFILE.USERID AS  username,\n" +
            "                            PROFILE.LANGPREF AS languagePreference,\n" +
            "                            PROFILE.FAVCATEGORY AS favouriteCategoryId,\n" +
            "                            PROFILE.MYLISTOPT AS listOption,\n" +
            "                            PROFILE.BANNEROPT AS bannerOption\n" +
            "                            FROM  PROFILE where PROFILE.USERID like #{keyword};")
    List<Account> searchAccountprofile(String keyword);
    @Delete("delete  from account where userid=#{username};")
    void deleteAccount(String username);
    @Delete("delete from signon where username=#{username};")
    void deleteSignon(String username);
    @Delete("delete  from profile where userid=#{username};")
    void deleteprofile(String username);
    @Insert("insert into wish values (#{username},#{wishItem});")
    void insertWish(Wish wish);
    @Delete("delete from wish where username=#{username} AND wishItem=#{wishItem};")
    void deleteWish(Wish wish);
    @Select("select * from wish where username=#{username};")
    List<Wish> selectWish(String username);
    @Select("SELECT username FROM userphone WHERE phone = #{phonenumber};")
    String getAccountnamebyphone(String phonenumber);
}
